package com.zbj.data.jpa.repository;

import com.zbj.data.jpa.entity.Teacher;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * TeacherRepository
 * https://docs.spring.io/spring-data/jpa/docs/1.11.6.RELEASE/reference/html/#repositories.create-instances.java-config @Transactional
 * 官网事务注解放在 Repository 层的
 *
 * @author weigang
 * @create 2017-09-12
 **/
@Transactional(readOnly = true)
public interface TeacherRepository extends JpaRepository<Teacher, Long>, JpaSpecificationExecutor<Teacher> {

	Page<Teacher> queryFirst2ByName(String name, Pageable pageable);

	List<Teacher> queryFirst10ByName(String name, Sort sort);

	/**
	 * 默认为1条 等于 findTop1ByNameOrderByAgeDesc
	 * @param name
	 * @return
	 */
	List<Teacher> findTopByNameOrderByAgeDesc(String name);

	Page<Teacher> findTop2ByName(String name, Pageable pageable);

	@Query(value = "select t from Teacher t where t.name = :name")
	List<Teacher> findByName(@Param("name") String name, Pageable pageable);

	@Query("select new Teacher(t.name, t.age, t.phone) from Teacher t where t.name = ?1")
	List<Teacher> findPartColumn(String name);

	@Query("select distinct new Teacher(t.name, t.phone) from Teacher t where t.name = ?1")
	List<Teacher> findDistinctPartColumn(String name);



	/**
	 * 走的是主库
	 * Modifying 不支持 insert
	 * @param phone
	 * @param name
	 * @return
	 */
	@Transactional
	@Modifying
	@Query(value = "update Teacher set phone = :phone where name = :name")
	int updateTeacherPhoneByName(@Param("phone") String phone, @Param("name") String name);

	@Transactional
	@Modifying
	@Query(value = "update teacher s set s.phone = ?1 where s.name = ?2", nativeQuery = true)
	int updatePhoneByName(String phone, String name);

	@Transactional
	@Modifying
	@Query(value = "delete from Teacher where name = :name")
	int deletePhoneByName(@Param("name") String name);
}
